package com.pb.infra.dynamicSetting.dynamicSettingTable.service;


import com.alibaba.fastjson.JSON;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.pb.infra.dynamicSetting.dynamTableCfg.entity.MaFrDynamTableCfg;
import com.pb.infra.dynamicSetting.dynamTableCfg.mapper.MaFrDynamTableCfgMapper;
import com.pb.infra.dynamicSetting.dynamTableColumnCfg.entity.MaFrDynamTableColumnCfg;
import com.pb.infra.dynamicSetting.dynamTableColumnCfg.mapper.MaFrDynamTableColumnCfgMapper;
import com.pb.infra.dynamicSetting.dynamicSettingTable.dto.TableSearchDTO;
import com.pb.infra.dynamicSetting.dynamicSettingTable.entity.DictVO;
import com.pb.infra.dynamicSetting.dynamicSettingTable.entity.DynamicTableOptionVO;
import com.pb.infra.dynamicSetting.dynamicSettingTable.mapper.DynamicSettingTableDataMapper;
import com.pb.infra.dynamicSetting.dynamicSettingTable.mapper.DynamicSettingTableMapper;
import com.pb.infra.oth.entity.common.Result;
import com.pb.infra.oth.entity.excel.Column;
import com.pb.infra.oth.entity.excel.Excel;
import com.pb.infra.oth.entity.excel.ExcelSheet;
import com.pb.infra.oth.util.ExportExcelUtil;
import com.pb.infra.oth.util.StringUtil;
import com.pb.infra.param.mapper.ImportConfigMapper;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;


import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author JYF
 */
@Service
public class DynamicSettingTableService {

    @Resource
    private DynamicSettingTableMapper dynamicSettingTableMapper;

    @Resource
    private DynamicSettingTableDataMapper dynamicSettingTableDataMapper;


    @Resource
    private MaFrDynamTableCfgMapper maFrDynamTableCfgMapper;

    @Resource
    private ImportConfigMapper importConfigMapper;

    @Resource
    private MaFrDynamTableColumnCfgMapper maFrDynamTableColumnCfgMapper;


    /**
     * 获取动态表格结构
     *
     * @param tableId 表格ID
     * @return 表格结构
     */
    public Result getTableStructure(String tableId) {
        DynamicTableOptionVO vo = dynamicSettingTableMapper.getTableStructure(tableId);

        vo.getColumn().forEach(item -> {
            if (!StringUtil.isEmpty(item.getPropsJson())) {
                DictVO dVO = JSON.parseObject(item.getPropsJson(), DictVO.class);
                item.setDictLabel(dVO.getLabel());
                item.setValue(dVO.getValue());
                item.setProps(dVO);
            }
        });


        return Result.ok(vo);
    }

    /**
     * 获取动态表格结构
     *
     * @param dto 表格ID
     * @return 表格结构
     */
    public Result tableDataList(TableSearchDTO dto) {
        MaFrDynamTableCfg maFrDynamTableCfg = maFrDynamTableCfgMapper.queryById(dto.getTableId());
        String tableName = "";
        StringBuilder sqlParam = new StringBuilder();
        StringBuilder sqlOrderParam = new StringBuilder();
        if (dto.getSearchParam().keySet().size()>0){
            sqlOrderParam.append(" order by ");
        }

        List<Map<String, String>> list = new ArrayList<>();
        // 分页
        Map<String, String> searchParam = dto.getSearchParam();
        for (String key: searchParam.keySet()) {
            if (key.indexOf("$") > -1) {
                searchParam.remove(key);
            }
        }
        dto.setSearchParam(searchParam);
        if (maFrDynamTableCfg != null) {
            tableName = maFrDynamTableCfg.getDestDataSchemaName() + "." + maFrDynamTableCfg.getTargetTableName();
            List<MaFrDynamTableColumnCfg> like = maFrDynamTableCfg.getColumn().stream().filter(MaFrDynamTableColumnCfg::getIsLike).collect(Collectors.toList());

            //拼接条件
            for (String key :
                    dto.getSearchParam().keySet()) {

                if(!key.contains("$")) {
                    //是否进行模糊查询
                    if (like.stream().anyMatch(item -> item.getProp().equals(key))) {
                        sqlParam.append(" and ").append(key).append(" like '%").append(dto.getSearchParam().get(key).replace("'","")).append("%' ");
                    } else {
                        if (dto.getSearchParam().get(key) != null && !dto.getSearchParam().get(key).equals("")) {
                            sqlParam.append(" and ").append(key).append("= '").append(dto.getSearchParam().get(key).replace("'","")).append("' ");
                        }

                    }
                    if (dto.getSearchParam().keySet().size()>0){
                        sqlOrderParam.append(key);
                    }

                    if (dto.getSearchParam().keySet().stream().reduce((first, second) -> second).get() != key) {
                        sqlOrderParam.append(",");
                    }
                }
            }
            if (dto.getSearchParam().keySet().size()>0){
                sqlOrderParam.append(" desc");
            }
            StringBuilder colSql = new StringBuilder();
            MaFrDynamTableColumnCfg maFrDynamTableColumnCfg = new MaFrDynamTableColumnCfg();
            maFrDynamTableColumnCfg.setTableId(dto.getTableId());
            List<MaFrDynamTableColumnCfg> maFtpReckonHpList = maFrDynamTableColumnCfgMapper.queryList(maFrDynamTableColumnCfg);
            for (int i = 0; i < maFtpReckonHpList.size(); i++) {
                if (i == maFtpReckonHpList.size() - 1) {
                    colSql.append(maFtpReckonHpList.get(i).getProp());
                } else {
                    colSql.append(maFtpReckonHpList.get(i).getProp() + ",");
                }
            }
            if (dto.getPage() != null && dto.getPage().getPageIndex() != null && dto.getPage().getPageSize() != null) {
                PageHelper.startPage(dto.getPage().getPageIndex(), dto.getPage().getPageSize());
            }
            list = dynamicSettingTableDataMapper.tableDataList(tableName, sqlParam.toString(), null, null, sqlOrderParam.toString(), colSql.toString());
        } else {
            return Result.error("500", "无效表ID");
        }
        return Result.ok(new PageInfo(list).getTotal(), list);
    }

    List<Map<String, Object>> getPkCol(DynamicTableOptionVO maFrDynamTableCfg) {
        Map<String, Object> colParam = new HashMap<>();

        colParam.put("dbUser", maFrDynamTableCfg.getDestDataSchemaName());
        colParam.put("tableName", maFrDynamTableCfg.getTargetTableName());
        List<Map<String, Object>> delParamKey = importConfigMapper.getColList(colParam).stream().filter(item -> item.get("pkFlag").toString().equals("1")).collect(Collectors.toList());

        return delParamKey;
    }

    /**
     * 删除
     *
     * @param tableId   表格Id
     * @param editParam 编辑对象
     * @return 结果
     */
     @Transactional
    public Result delTableData(String tableId, Map<String, String> editParam) {
        DynamicTableOptionVO maFrDynamTableCfg = dynamicSettingTableMapper.getTableStructure(tableId);

        if (maFrDynamTableCfg != null) {
            List<Map<String, Object>> delParamKey = this.getPkCol(maFrDynamTableCfg);
            String tableName = maFrDynamTableCfg.getDestDataSchemaName() + "." + maFrDynamTableCfg.getTargetTableName();
            if (delParamKey != null && delParamKey.size() > 0) {
                String delParam = "";
                for (Map<String, Object> item :
                        delParamKey) {
                    delParam = delParam + " " + item.get("columnName") + " = '" + editParam.get(item.get("columnName")) + "' and ";
                }

                return Result.ok(dynamicSettingTableDataMapper.delTableData(tableName, delParam.substring(0, delParam.length() - 5)));
            } else {
                return Result.error("500", "删除失败，未设置主键");
            }
        } else {
            return Result.error("500", "删除失败，未找到删除对象");
        }

    }

    /**
     * 添加
     *
     * @param tableId 表格Id
     * @param map     添加对象
     * @return 结果
     */
     @Transactional
    public Result addTableData(String tableId, Map<String, String> map) {
        DynamicTableOptionVO maFrDynamTableCfg = dynamicSettingTableMapper.getTableStructure(tableId);
        String sql = "";
        if (maFrDynamTableCfg != null) {
            List<String> columns = new ArrayList<>();
            List<String> values = new ArrayList<>();
            List<Map<String, Object>> paramKey = this.getPkCol(maFrDynamTableCfg);
            if (paramKey != null && paramKey.size() > 0) {
                StringBuilder selSql = new StringBuilder();
                selSql.append("select count(1) from ").append(maFrDynamTableCfg.getDestDataSchemaName()).append(".").append(maFrDynamTableCfg.getTargetTableName());
                for (Map<String, Object> item: paramKey) {
                    selSql.append(" where ").append(item.get("columnName")).append(" = ").append(map.get(item.get("columnName")));
                }
                String selectSql = selSql.substring(0, selSql.length() - 5);
                try {
                    int numTable = dynamicSettingTableDataMapper.getNumTable(selectSql);
                    if (numTable > 0) {
                        return Result.error("500", "添加失败，主键重复");
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            maFrDynamTableCfg.getColumn().forEach(item -> {
                if (map.get(item.getProp()) != null) {
                    columns.add(item.getProp());
                    values.add("'" + map.get(item.getProp()) + "'");
                }

            });

            sql = "insert into " + maFrDynamTableCfg.getDestDataSchemaName() + "." + maFrDynamTableCfg.getTargetTableName()
                    + " (" + StringUtils.strip(columns.toString(), "[]") + ") VALUES (" + StringUtils.strip(values.toString(), "[]") + ") ";
        } else {
            return Result.error("500", "添加失败，无效表ID");
        }
        return Result.ok(dynamicSettingTableDataMapper.addTableData(sql));
    }

    /**
     * 修改
     *
     * @param tableId 表格Id
     * @param map     添加对象
     * @return 结果
     */
     @Transactional
    public Result updTableData(String tableId, Map<String, String> map) {
        DynamicTableOptionVO maFrDynamTableCfg = dynamicSettingTableMapper.getTableStructure(tableId);
        String sql = "";
        if (maFrDynamTableCfg != null) {
            List<Map<String, Object>> updParamKey = this.getPkCol(maFrDynamTableCfg);


            if (updParamKey != null && updParamKey.size() > 0) {
                String tableName = maFrDynamTableCfg.getDestDataSchemaName() + "." + maFrDynamTableCfg.getTargetTableName();
                String whereParam = "";
                String setColumns = "";
                StringBuilder finalSetColumns = new StringBuilder(setColumns);
                StringBuilder finalWhereParam = new StringBuilder(whereParam);


                for (MaFrDynamTableColumnCfg item : maFrDynamTableCfg.getColumn()) {
                    finalSetColumns.append(item.getProp()).append(" = '").append(map.get(item.getProp())).append("' , ");
                }

                for (Map<String, Object> item :
                        updParamKey) {
                    finalWhereParam.append(" ").append(item.get("columnName")).append(" = '").append(map.get(item.get("columnName"))).append("' and ");
                }


                setColumns = finalSetColumns.substring(0, finalSetColumns.length() - 2);

                whereParam = finalWhereParam.substring(0, finalWhereParam.length() - 5);

                return Result.ok(dynamicSettingTableDataMapper.updTableData(tableName, setColumns, whereParam));

            } else {
                return Result.error("500", "修改失败，未设置主键");
            }
        } else {
            return Result.error("500", "修改失败，无效表ID");
        }

    }

    public void dataExport(TableSearchDTO dto, HttpServletResponse response) throws Exception {
//        获取数据
        List<Map<String, String>> list = (List<Map<String, String>>) this.tableDataList(dto).getData();
        Map<String, Object> paramMap = new HashMap<>();
        paramMap.put("databaseUserId", dto.getDataBaseUserId());
        paramMap.put("datasourceId", dto.getDataSourceId());
        List<Map<String, Object>> headerList = this.getRightTableData(paramMap);
        List<Map<String, String>> sheetList = new ArrayList<>();
        ExcelSheet[] sheets = new ExcelSheet[sheetList.size() + 1];
        ExcelSheet sheet = new ExcelSheet();
        sheet.setSheetName("数据");
        String[][] headers = new String[1][headerList.size()];
        headers[0] = new String[headerList.size()];
        Column[] columns = new Column[headerList.size()];
        String[] comments = new String[headerList.size()];
        for (int i = 0; i < headerList.size(); i++) {
            headers[0][i] = headerList.get(i).get("CN_NAME").toString();
            columns[i] = new Column(headerList.get(i).get("COLUMN_NAME").toString());
            if (!(boolean)headerList.get(i).get("NULL_FLAG")) {
                comments[i] = "必填";
            } else {
                comments[i] = "";
            }
        }
        sheet.setHeader(headers);
        sheet.setColumn(columns);
        sheet.setData(list);
        sheet.setComment(comments);
        sheets[0] = sheet;
        ExportExcelUtil.exportExcel2007(new Excel("数据"), sheets, response);
    }

    /**
     * 获取配置表右表数据
     *
     * @param paramMap
     * @return
     */
    public List<Map<String, Object>> getRightTableData(Map<String, Object> paramMap) {
        List<Map<String, Object>> list = dynamicSettingTableMapper.getRightTableData(paramMap);
        for (Map<String, Object> map : list) {
            map.put("PK_FLAG", "1".equals((String) map.get("PK_FLAG")) ? true : false);
            map.put("NULL_FLAG", "1".equals((String) map.get("NULL_FLAG")) ? true : false);
            map.put("BK_FLAG", "1".equals((String) map.get("BK_FLAG")) ? true : false);
            map.put("REMARK_FLAG", "1".equals((String) map.get("REMARK_FLAG")) ? true : false);
            map.put("DIMENSION_FLAG", "1".equals((String) map.get("DIMENSION_FLAG")) ? true : false);
            map.put("METRIC_FLAG", "1".equals((String) map.get("METRIC_FLAG")) ? true : false);
            map.put("PATITION_KEY_FLAG", "1".equals((String) map.get("PATITION_KEY_FLAG")) ? true : false);
        }
        return list;
    }

    public Result queryIMPId() {
        return Result.ok(dynamicSettingTableMapper.queryImpId());
    }
}
